package service;

import java.sql.*;

public class SQLService {
    static final String DB_URL = "jdbc:mysql://localhost:3306/lottery?characterEncoding=UTF-8&useSSL=false";
    static final String USER = "root";
    static final String PASS = "12345678c";

    public static void write(String addr, String prize) {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            PreparedStatement sql;
            sql = conn.prepareStatement("INSERT INTO `result` (`ip`, `prize`) VALUES ('" + addr + "'" + ", '" + prize + "')");
            sql.executeUpdate();
            conn.close();
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// 什么都不做
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

    public static String[][] read() {
        Connection conn = null;
        Statement stmt = null;
        String[][] res = new String[10][2];
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            PreparedStatement sql = conn.prepareStatement("SELECT * FROM `result` WHERE 1");
            ResultSet rs = sql.executeQuery();
            rs.last();
            int num = Integer.parseInt(rs.getString("id"));
            sql = conn.prepareStatement("SELECT * FROM `result` WHERE id > " + (num - 8));
            rs = sql.executeQuery();
            int i = 0;
            while(rs.next() && i < 8){
                res[i][0] = rs.getString("ip");
                res[i][1] = rs.getString("prize");
                i++;
            }
            rs.close();
            conn.close();
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// 什么都不做
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return res;
    }
}
